import pandas as pd
import pymysql
from sqlalchemy import create_engine
import sqlalchemy
from pyecharts.charts import Line, Page
import pyecharts.options as opts
from pyecharts.globals import ThemeType
import sys
sys.path.append("..//dataprocessor")
from CmnFuncs import *
import tushare as ts
pro = ts.pro_api()
import akshare as ak


def pull_cpi():
    cpi_df = ts.get_cpi()
    ind = cpi_df.index.to_list()
    for i in ind:
        l = cpi_df.loc[i, 'month'].split('.')
        cpi_df.loc[i, 'month'] = str(pd.Period("{}-{}".format(l[0], l[1])))
    cpi_df.set_index('month', inplace=True)
    cpi_df.sort_index(inplace=True)
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    cpi_df.to_sql(name='funda_cpi', con=engine, if_exists='replace',
                  dtype={'month': sqlalchemy.types.VARCHAR(cpi_df.index.get_level_values('month').str.len().max())})

def pull_ppi():
    ppi_df = ts.get_ppi()
    ind = ppi_df.index.to_list()
    for i in ind:
        l = ppi_df.loc[i, 'month'].split('.')
        ppi_df.loc[i, 'month'] = str(pd.Period("{}-{}".format(l[0], l[1])))
    ppi_df.set_index('month', inplace=True)
    ppi_df.sort_index(inplace=True)
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    ppi_df.to_sql(name='funda_ppi', con=engine, if_exists='replace',
                  dtype={'month': sqlalchemy.types.VARCHAR(ppi_df.index.get_level_values('month').str.len().max())})


def pull_money_supply():
    ms_df = ts.get_money_supply()
    ind = ms_df.index.to_list()
    for i in ind:
        l = ms_df.loc[i, 'month'].split('.')
        ms_df.loc[i, 'month'] = str(pd.Period("{}-{}".format(l[0], l[1])))
    ms_df.set_index('month', inplace=True)
    ms_df.sort_index(inplace=True)
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    ms_df.to_sql(name='funda_moneysupply', con=engine, if_exists='replace',
                  dtype={'month': sqlalchemy.types.VARCHAR(ms_df.index.get_level_values('month').str.len().max())})

def pull_rrr():
    rrr_df = ts.get_rrr()
    rrr_df.set_index('date', inplace=True)
    rrr_df.sort_index(inplace=True)
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    rrr_df.to_sql(name='funda_rrr', con=engine, if_exists='replace',
                  dtype={'date': sqlalchemy.types.VARCHAR(rrr_df.index.get_level_values('date').str.len().max())})

def pull_lpr(start, end):
    '''由于LPR需要赋时间，所以采取追加模式写入'''
    lpr_df = pro.shibor_lpr(start_date=start, end_date=end)
    lpr_df.set_index('date', inplace=True)
    lpr_df.sort_index(inplace=True)
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    try:
        lpr_df.to_sql(name='funda_lpr', con=engine, if_exists='append',
                      dtype={'date': sqlalchemy.types.VARCHAR(lpr_df.index.get_level_values('date').str.len().max())})
    except:
        print('LPR write in error detected...')
    else:
        pass

def pull_shibor(start, end):
    '''shibor由于需要赋时间，所以采用追加模式写入'''
    shibor_df = pro.shibor(start_date=start, end_date=end)
    shibor_df.set_index('date', inplace=True)
    shibor_df.sort_index(inplace=True)
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    try:
        shibor_df.to_sql(name='funda_shibor', con=engine, if_exists='append',
                        dtype={'date': sqlalchemy.types.VARCHAR(shibor_df.index.get_level_values('date').str.len().max())})
    except:
        print('SHIBOR write in error detected...')
    else:
        pass

def pull_leverage():
    macro_df = ak.macro_cnbs()
    macro_df.set_index('年份', inplace=True)
    macro_df.sort_index(inplace=True)
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    try:
        macro_df.to_sql(name='funda_cn_lever', con=engine, if_exists='replace',
                        dtype={'年份': sqlalchemy.types.VARCHAR(
                            macro_df.index.get_level_values('年份').str.len().max())})
    except:
        print('CN_leverage write in error detected...')
    else:
        pass

def pull_us_ir():
    index_se = ak.macro_bank_usa_interest_rate()
    ir_df = pd.DataFrame(index_se)
    ir_df.index = ir_df.index.map(lambda x: x.strftime('%Y-%m-%d'))
    ir_df.index.name = 'index'
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    try:
        ir_df.to_sql(name='funda_us_ir', con=engine, if_exists='replace',
                     dtype={'index': sqlalchemy.types.VARCHAR(
                            ir_df.index.get_level_values('index').str.len().max())})
    except:
        print('US_interest_rate write in error detected...')
    else:
        pass

def pull_us_nf():
    index_se = ak.macro_usa_non_farm()
    nf_df = pd.DataFrame(index_se)
    nf_df.index = nf_df.index.map(lambda x: x.strftime('%Y-%m-%d'))
    nf_df.index.name = 'index'
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    try:
        nf_df.to_sql(name='funda_us_nf', con=engine, if_exists='replace',
                     dtype={'index': sqlalchemy.types.VARCHAR(
                        nf_df.index.get_level_values('index').astype(str).str.len().max())})
    except:
        print('US_non_farm write in error detected...')
    else:
        pass

def pull_us_ur():
    index_se = ak.macro_usa_unemployment_rate()
    ur_df = pd.DataFrame(index_se)
    ur_df.index = ur_df.index.map(lambda x: x.strftime('%Y-%m-%d'))
    ur_df.index.name = 'index'
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    try:
        ur_df.to_sql(name='funda_us_ur', con=engine, if_exists='replace',
                     dtype={'index': sqlalchemy.types.VARCHAR(
                         ur_df.index.get_level_values('index').str.len().max())})
    except:
        print('US_unemployment_rate write in error detected...')
    else:
        pass

def pull_us_eia():
    index_se = ak.macro_usa_eia_crude_rate()
    eia_df = pd.DataFrame(index_se)
    eia_df.index = eia_df.index.map(lambda x: x.strftime('%Y-%m-%d'))
    eia_df.index.name = 'index'
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    try:
        eia_df.to_sql(name='funda_us_eia_crude', con=engine, if_exists='replace',
                      dtype={'index': sqlalchemy.types.VARCHAR(eia_df.index.get_level_values('index').str.len().max())})
    except:
        print('US_EIA_crude write in error detected...')
    else:
        pass

def draw_us_ir():
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    ir_df = pd.read_sql_table('funda_us_ir', con=engine, index_col='index')
    datelist = ir_df.index.to_list()
    ir = ir_df['usa_interest_rate'].to_list()
    line = (
        Line()
        .add_xaxis(datelist)
        .add_yaxis('interest_rate', ir)
        .set_global_opts(title_opts=opts.TitleOpts(title="美联储利率决议"),
                         yaxis_opts=opts.AxisOpts(is_scale=True), toolbox_opts=opts.ToolboxOpts(),
                         datazoom_opts=opts.DataZoomOpts(range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
        .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
    )
    line.width = '900px'
    line.height = '400px'
    return line

def draw_us_nf():
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    nf_df = pd.read_sql_table('funda_us_nf', con=engine, index_col='index')
    datelist = nf_df.index.to_list()
    nf = nf_df['non_farm'].to_list()
    line = (
        Line()
        .add_xaxis(datelist)
        .add_yaxis('non_farm_payroll', nf)
        .set_global_opts(title_opts=opts.TitleOpts(title="美国非农就业"),
                         yaxis_opts=opts.AxisOpts(is_scale=True), toolbox_opts=opts.ToolboxOpts(),
                         datazoom_opts=opts.DataZoomOpts(range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
        .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
    )
    line.width = '900px'
    line.height = '400px'
    return line

def draw_us_ur():
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    ur_df = pd.read_sql_table('funda_us_ur', con=engine, index_col='index')
    datelist = ur_df.index.to_list()
    ur = ur_df['unemployment_rate'].to_list()
    line = (
        Line()
        .add_xaxis(datelist)
        .add_yaxis('unemployment_rate', ur)
        .set_global_opts(title_opts=opts.TitleOpts(title="美国失业率报告"),
                         yaxis_opts=opts.AxisOpts(is_scale=True), toolbox_opts=opts.ToolboxOpts(),
                         datazoom_opts=opts.DataZoomOpts(range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
        .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
    )
    line.width = '900px'
    line.height = '400px'
    return line

def draw_cn_lever():
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    lever_df = pd.read_sql_table('funda_cn_lever', con=engine, index_col='年份')
    datelist = lever_df.index.to_list()
    civi = lever_df['居民部门'].to_list()
    non_fi = lever_df['非金融企业部门'].to_list()
    gov = lever_df['政府部门'].to_list()
    centrgov = lever_df['中央政府'].to_list()
    localgov = lever_df['地方政府'].to_list()
    realeco = lever_df['实体经济部门'].to_list()
    fi_asset = lever_df['金融部门资产方'].to_list()
    fi_lia = lever_df['金融部门负债方'].to_list()
    line = (
        Line()
        .add_xaxis(datelist)
        .add_yaxis('居民部门', civi)
        .add_yaxis('非金融企业部门', non_fi)
        .add_yaxis('政府部门', gov)
        .add_yaxis('中央政府', centrgov)
        .add_yaxis('地方政府', localgov)
        .add_yaxis('实体经济部门', realeco)
        .add_yaxis('金融部门资产方', fi_asset)
        .add_yaxis('金融部门负债方', fi_lia)
        .set_global_opts(title_opts=opts.TitleOpts(title="中国宏观杠杆率"),
                         yaxis_opts=opts.AxisOpts(is_scale=True), toolbox_opts=opts.ToolboxOpts(),
                         legend_opts=opts.LegendOpts(pos_left='15%', pos_top='10%'),
                         datazoom_opts=opts.DataZoomOpts(range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
        .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
    )
    line.width = '900px'
    line.height = '400px'
    return line

def draw_cpi_ppi():
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    cpi_df = pd.read_sql_table('funda_cpi', con=engine, index_col='month')['cpi']
    ppi_df = pd.read_sql_table('funda_ppi', con=engine, index_col='month')['ppiip']
    datelist = (cpi_df + ppi_df).index.to_list()
    pcs, pps = [], []
    for date in datelist:
        try:
            pc = cpi_df[date]
        except:
            pc = None
        else:
            pass
        pcs.append(pc)

        try:
            pp = ppi_df[date]
        except:
            pp = None
        else:
            pass
        pps.append(pp)

    line = Line(init_opts=opts.InitOpts(theme=ThemeType.SHINE))
    line.add_xaxis(datelist)
    line.add_yaxis('CPI', pcs)
    line.set_global_opts(title_opts=opts.TitleOpts(title="CPI/PPI对照图"),  toolbox_opts=opts.ToolboxOpts(),
                         legend_opts=opts.LegendOpts(pos_left='15%', pos_top='10%'),
                         yaxis_opts=opts.AxisOpts(is_scale=True), datazoom_opts=opts.DataZoomOpts(range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
    line.set_series_opts(label_opts=opts.LabelOpts(is_show=False))
    line.extend_axis(yaxis=opts.AxisOpts(is_scale=True))

    line_2 = Line()
    line_2.add_xaxis(datelist)
    line_2.add_yaxis('PPI', pps, yaxis_index=1)
    line_2.set_series_opts(label_opts=opts.LabelOpts(is_show=False))

    line.overlap(line_2)
    line.width = '900px'
    line.height = '400px'
    return line

def draw_ppi_items():
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    ppi_df = pd.read_sql_table('funda_ppi', con=engine, index_col='month')
    datelist = ppi_df.index.to_list()
    ppiip = ppi_df['ppiip'].to_list()
    ppi = ppi_df['ppi'].to_list()
    qm = ppi_df['qm'].to_list()
    rmi = ppi_df['rmi'].to_list()
    pi = ppi_df['pi'].to_list()
    cg = ppi_df['cg'].to_list()
    food = ppi_df['food'].to_list()
    clothing = ppi_df['clothing'].to_list()
    roeu = ppi_df['roeu'].to_list()
    dcg = ppi_df['dcg'].to_list()

    line = Line(init_opts=opts.InitOpts(theme=ThemeType.SHINE))
    line.add_xaxis(datelist)
    line.add_yaxis('PPI', ppiip)
    line.add_yaxis('生产资料', ppi)
    line.add_yaxis('采掘工业', qm)
    line.add_yaxis('原材料工业', rmi)
    line.add_yaxis('加工工业', pi)
    line.add_yaxis('生活资料', cg)
    line.add_yaxis('食品类', food)
    line.add_yaxis('衣着类', clothing)
    line.add_yaxis('一般日用品', roeu)
    line.add_yaxis('耐用消费品', dcg)
    line.set_global_opts(title_opts=opts.TitleOpts(title="PPI",
                                                   subtitle="各分项价格指数",
                                                   subtitle_textstyle_opts=opts.TextStyleOpts(font_size=12)),
                         toolbox_opts=opts.ToolboxOpts(),
                         legend_opts=opts.LegendOpts(pos_left='15%', pos_top='10%'),
                         yaxis_opts=opts.AxisOpts(is_scale=True), datazoom_opts=opts.DataZoomOpts(range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
    line.set_series_opts(label_opts=opts.LabelOpts(is_show=False))

    line.width = '900px'
    line.height = '400px'

    return line

def draw_ms_items():
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    ms_df = pd.read_sql_table('funda_moneysupply', con=engine, index_col='month')
    datelist = ms_df.index.to_list()
    def trans_to_nums(dlist, df, name):
        rlist = []
        for date in dlist:
            old = df.loc[date, name]
            try:
                new = float(old)
            except:
                new = None
            else:
                pass
            rlist.append(new)
        return rlist
    m2 = trans_to_nums(datelist, ms_df, 'm2')
    m1 = trans_to_nums(datelist, ms_df, 'm1')
    m0 = trans_to_nums(datelist, ms_df, 'm0')
    cd = trans_to_nums(datelist, ms_df, 'cd')
    qm = trans_to_nums(datelist, ms_df, 'qm')
    ftd = trans_to_nums(datelist, ms_df, 'ftd')
    sd = trans_to_nums(datelist, ms_df, 'sd')
    rests = trans_to_nums(datelist, ms_df, 'rests')
    scissors = []
    for i in range(len(datelist)):
        try:
            scissor = m2[i] - m1[i]
        except:
            scissor = None
        else:
            pass
        scissors.append(scissor)
    line = Line(init_opts=opts.InitOpts(theme=ThemeType.SHINE))
    line.add_xaxis(datelist)
    line.add_yaxis('广义货币M2', m2)
    line.add_yaxis('狭义货币M1', m1)
    line.add_yaxis('流通中现金M0', m0)
    line.add_yaxis('活期存款', cd)
    line.add_yaxis('准货币', qm)
    line.add_yaxis('定期存款', ftd)
    line.add_yaxis('储蓄存款', sd)
    line.add_yaxis('其他存款', rests)
    line.extend_axis(yaxis=opts.AxisOpts(is_scale=True))
    line.add_yaxis('M2-M1剪刀差', scissors, yaxis_index=1)
    line.set_global_opts(title_opts=opts.TitleOpts(title="货币供应",
                                                   subtitle="各分项数据",
                                                   subtitle_textstyle_opts=opts.TextStyleOpts(font_size=12)),
                         toolbox_opts=opts.ToolboxOpts(),
                         legend_opts=opts.LegendOpts(pos_left='15%', pos_top='10%'),
                         yaxis_opts=opts.AxisOpts(is_scale=True), datazoom_opts=opts.DataZoomOpts(range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
    line.set_series_opts(label_opts=opts.LabelOpts(is_show=False))

    line.width = '900px'
    line.height = '400px'

    return line

def draw_ms_ratio_items():
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    ms_df = pd.read_sql_table('funda_moneysupply', con=engine, index_col='month')
    datelist = ms_df.index.to_list()

    def trans_to_nums(dlist, df, name):
        rlist = []
        for date in dlist:
            old = df.loc[date, name]
            try:
                new = float(old)
            except:
                new = None
            else:
                pass
            rlist.append(new)
        return rlist
    m2yoy = trans_to_nums(datelist, ms_df, 'm2_yoy')
    m1yoy = trans_to_nums(datelist, ms_df, 'm1_yoy')
    m0yoy = trans_to_nums(datelist, ms_df, 'm0_yoy')

    m2rm1 = []
    for i in range(len(datelist)):
        try:
            r = m2yoy[i] / m1yoy[i]
        except:
            r = None
        else:
            pass
        m2rm1.append(r)

    line = Line(init_opts=opts.InitOpts(theme=ThemeType.SHINE))
    line.add_xaxis(datelist)
    line.add_yaxis('广义货币M2同比增长率', m2yoy)
    line.add_yaxis('狭义货币M1同比增长率', m1yoy)
    line.add_yaxis('流通中现金M0同比增长率', m0yoy)
    line.extend_axis(yaxis=opts.AxisOpts(is_scale=True))
    line.add_yaxis('M2相对M1同比增长率的比', m2rm1, yaxis_index=1)
    line.set_global_opts(title_opts=opts.TitleOpts(title="货币供应",
                                                   subtitle="各分项同比增长率",
                                                   subtitle_textstyle_opts=opts.TextStyleOpts(font_size=12)),
                         toolbox_opts=opts.ToolboxOpts(),
                         legend_opts=opts.LegendOpts(pos_left='15%', pos_top='10%'),
                         yaxis_opts=opts.AxisOpts(is_scale=True), datazoom_opts=opts.DataZoomOpts(range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
    line.set_series_opts(label_opts=opts.LabelOpts(is_show=False))

    line.width = '900px'
    line.height = '400px'

    return line

def draw_rrr():
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    rrr_df = pd.read_sql_table('funda_rrr', con=engine, index_col='date')['now']
    datelist = rrr_df.index.to_list()
    rrr = []
    for date in datelist:
        try:
            r = rrr_df[date]
        except:
            r = None
        else:
            pass
        rrr.append(r)

    line = Line(init_opts=opts.InitOpts(theme=ThemeType.SHINE))
    line.add_xaxis(datelist)
    line.add_yaxis('存款准备金率', rrr)
    line.set_global_opts(title_opts=opts.TitleOpts(title="存款准备金率"),  toolbox_opts=opts.ToolboxOpts(),
                         legend_opts=opts.LegendOpts(pos_left='15%', pos_top='10%'),
                         yaxis_opts=opts.AxisOpts(is_scale=True), datazoom_opts=opts.DataZoomOpts(range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
    line.set_series_opts(label_opts=opts.LabelOpts(is_show=False))
    line.extend_axis(yaxis=opts.AxisOpts(is_scale=True))

    line.width = '900px'
    line.height = '400px'
    return line

def draw_shibor_and_lpr():
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    sb_df = pd.read_sql_table('funda_shibor', con=engine, index_col='date')
    lpr_df = pd.read_sql_table('funda_lpr', con=engine, index_col='date')
    datelist = (sb_df+lpr_df).index.to_list()

    def trans_to_nums(dlist, df, name):
        rlist = []
        for date in dlist:
            try:
                v = df.loc[date, name]
            except:
                v = None
            else:
                pass
            rlist.append(v)
        return rlist
    on = trans_to_nums(datelist, sb_df, 'on')
    w1 = trans_to_nums(datelist, sb_df, '1w')
    w2 = trans_to_nums(datelist, sb_df, '2w')
    m1 = trans_to_nums(datelist, sb_df, '1m')
    m3 = trans_to_nums(datelist, sb_df, '3m')
    m6 = trans_to_nums(datelist, sb_df, '6m')
    m9 = trans_to_nums(datelist, sb_df, '9m')
    y1 = trans_to_nums(datelist, sb_df, '1y')
    y1_lpr = trans_to_nums(datelist, lpr_df, '1y')

    line = Line(init_opts=opts.InitOpts(theme=ThemeType.SHINE))
    line.add_xaxis(datelist)
    line.add_yaxis('隔夜', on)
    line.add_yaxis('一周', w1)
    line.add_yaxis('两周', w2)
    line.add_yaxis('一个月', m1)
    line.add_yaxis('三个月', m3)
    line.add_yaxis('六个月', m6)
    line.add_yaxis('九个月', m9)
    line.add_yaxis('一年', y1)
    line.add_yaxis('LPR一年期', y1_lpr, is_connect_nones=True)
    line.set_global_opts(title_opts=opts.TitleOpts(title="SHIBOR及LPR",
                                                   subtitle="各分项数据",
                                                   subtitle_textstyle_opts=opts.TextStyleOpts(font_size=12)),
                         toolbox_opts=opts.ToolboxOpts(),
                         legend_opts=opts.LegendOpts(pos_left='15%', pos_top='10%'),
                         yaxis_opts=opts.AxisOpts(is_scale=True), datazoom_opts=opts.DataZoomOpts(range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
    line.set_series_opts(label_opts=opts.LabelOpts(is_show=False))

    line.width = '900px'
    line.height = '400px'

    return line

def draw_pmim_items():
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    m_df = pd.read_sql_table('funda_pmi_m', con=engine, index_col='month')
    datelist = m_df.index.to_list()
    def trans_to_nums(dlist, df, name):
        rlist = []
        for date in dlist:
            old = df.loc[date, name]
            try:
                new = float(old)
            except:
                new = None
            else:
                pass
            rlist.append(new)
        return rlist
    pmi = trans_to_nums(datelist, m_df, 'pmi')
    prod = trans_to_nums(datelist, m_df, 'production')
    no = trans_to_nums(datelist, m_df, 'new_order')
    rs = trans_to_nums(datelist, m_df, 'raw_stocks')
    em = trans_to_nums(datelist, m_df, 'employee')
    sd = trans_to_nums(datelist, m_df, 'supplier_delivery')
    neo = trans_to_nums(datelist, m_df, 'new_export_order')
    im = trans_to_nums(datelist, m_df, 'import')
    pa = trans_to_nums(datelist, m_df, 'procurement_amount')
    mrpp = trans_to_nums(datelist, m_df, 'main_raw_purchase_price')
    pp = trans_to_nums(datelist, m_df, 'product_price')
    ps = trans_to_nums(datelist, m_df, 'product_stocks')
    oah = trans_to_nums(datelist, m_df, 'order_at_hand')
    bo = trans_to_nums(datelist, m_df, 'business_outlook')


    line = Line(init_opts=opts.InitOpts(theme=ThemeType.SHINE))
    line.add_xaxis(datelist)
    line.add_yaxis('PMI_M', pmi)
    line.add_yaxis('生产', prod)
    line.add_yaxis('新订单', no)
    line.add_yaxis('原材料库存', rs)
    line.add_yaxis('从业人员', em)
    line.add_yaxis('供应商配送时间', sd)
    line.add_yaxis('新出口订单', neo)
    line.add_yaxis('进口', im)
    line.add_yaxis('采购量', pa)
    line.add_yaxis('主要原料购进价格', mrpp)
    line.add_yaxis('出厂价格', pp)
    line.add_yaxis('产成品库存', ps)
    line.add_yaxis('在手订单', oah)
    line.add_yaxis('经营预期', bo)
    line.set_global_opts(title_opts=opts.TitleOpts(title="NBS制造业PMI",
                                                   subtitle="各分项数据",
                                                   subtitle_textstyle_opts=opts.TextStyleOpts(font_size=12)),
                         toolbox_opts=opts.ToolboxOpts(),
                         legend_opts=opts.LegendOpts(pos_left='15%', pos_top='10%'),
                         yaxis_opts=opts.AxisOpts(is_scale=True), datazoom_opts=opts.DataZoomOpts(range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
    line.set_series_opts(label_opts=opts.LabelOpts(is_show=False),
                         markline_opts=opts.MarkLineOpts(data=[opts.MarkLineItem(type_='50line', name='荣枯线', y=50)]))

    line.width = '900px'
    line.height = '400px'

    return line

def draw_pminm_items():
    # engine = create_engine("mysql+pymysql://user:password@host:port/databasename?charset=utf8")
    engine = create_engine("mysql+pymysql://root:sqlpass@localhost:3306/tradedata?charset=utf8mb4")
    nm_df = pd.read_sql_table('funda_pmi_nm', con=engine, index_col='month')
    datelist = nm_df.index.to_list()
    def trans_to_nums(dlist, df, name):
        rlist = []
        for date in dlist:
            old = df.loc[date, name]
            try:
                new = float(old)
            except:
                new = None
            else:
                pass
            rlist.append(new)
        return rlist
    pmi = trans_to_nums(datelist, nm_df, 'pmi')
    no = trans_to_nums(datelist, nm_df, 'new_order')
    ip = trans_to_nums(datelist, nm_df, 'input_price')
    sp = trans_to_nums(datelist, nm_df, 'sales_price')
    em = trans_to_nums(datelist, nm_df, 'employee')
    bo = trans_to_nums(datelist, nm_df, 'business_outlook')
    neo = trans_to_nums(datelist, nm_df, 'new_export_order')
    oah = trans_to_nums(datelist, nm_df, 'order_at_hand')
    st = trans_to_nums(datelist, nm_df, 'stocks')
    sd = trans_to_nums(datelist, nm_df, 'supplier_delivery')


    line = Line(init_opts=opts.InitOpts(theme=ThemeType.SHINE))
    line.add_xaxis(datelist)
    line.add_yaxis('PMI_NM', pmi)
    line.add_yaxis('新订单', no)
    line.add_yaxis('投入品价格', ip)
    line.add_yaxis('销售价格', sp)
    line.add_yaxis('从业人员', em)
    line.add_yaxis('业务预期', bo)
    line.add_yaxis('新出口订单', neo)
    line.add_yaxis('在手订单', oah)
    line.add_yaxis('存货', st)
    line.add_yaxis('供应商配送时间', sd)
    line.set_global_opts(title_opts=opts.TitleOpts(title="NBS非制造业PMI",
                                                   subtitle="各分项数据",
                                                   subtitle_textstyle_opts=opts.TextStyleOpts(font_size=12)),
                         toolbox_opts=opts.ToolboxOpts(),
                         legend_opts=opts.LegendOpts(pos_left='15%', pos_top='10%'),
                         yaxis_opts=opts.AxisOpts(is_scale=True), datazoom_opts=opts.DataZoomOpts(range_end=100),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
    line.set_series_opts(label_opts=opts.LabelOpts(is_show=False),
                         markline_opts=opts.MarkLineOpts(data=[opts.MarkLineItem(type_='50line', name='荣枯线', y=50)]))

    line.width = '900px'
    line.height = '400px'

    return line

def show_fundamentals():
    cpi_ppi = draw_cpi_ppi()
    ppi_items = draw_ppi_items()
    ms_items = draw_ms_items()
    ms_ratio = draw_ms_ratio_items()
    rrr = draw_rrr()
    shibor_lpr = draw_shibor_and_lpr()
    m = draw_pmim_items()
    nm = draw_pminm_items()
    cnlv = draw_cn_lever()
    usir = draw_us_ir()
    usnf = draw_us_nf()
    usur = draw_us_ur()
    s1 = draw_separator('中国宏观')
    s2 = draw_separator('美国宏观')
    page = Page(page_title='宏观基本面数据', layout=Page.SimplePageLayout)
    page.add(
        s1,
        ms_items,
        ms_ratio,
        rrr,
        shibor_lpr,
        m,
        nm,
        cnlv,
        cpi_ppi,
        ppi_items,
        s2,
        usir,
        usnf,
        usur
    )
    page.render('宏观基本面数据.html')


